﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace OutLook.Module.Load
{
    //Microsoft Office using
    using Microsoft.Office.Interop.Excel;
    using OutLook.Model.Domains;
    using OutLook.Model.Infrastructure;
    using System.Globalization;

    public static class XlsToXml
    {
        public static void ConvertXlsToXml(string filename, out Load ExcelLoad, out Technology ExcelTehnology, out ElementsDistribution ExcelDistribution, out Model ExcelModel)
        {
            ExcelLoad = new Load();
            ExcelTehnology = null;
            ExcelDistribution = null;
            ExcelModel = null;
            try
            {
                ObjExcel = new Microsoft.Office.Interop.Excel.Application();
                ObjWorkBook = ObjExcel.Workbooks.Open(filename);
                ObjWorkSheet = ObjExcel.Worksheets[1] as Microsoft.Office.Interop.Excel.Worksheet;

                Range excelRange = ObjWorkSheet.UsedRange;

                object[,] valueArray = (object[,])excelRange.get_Value(XlRangeValueDataType.xlRangeValueDefault);

                List<ChargeRowToLoad> ExcelChargeRowToLoad = new List<ChargeRowToLoad>();

                string AshName = default(string);
                double AshValue = default(double);
                double AshChargeWeight = default(double);
                double AshWet = default(double);
                double AshVolatiles = default(double);

                for (int i = 2; i <= valueArray.GetUpperBound(0); i++)
                {
                    List<ChemicalСombination> ExcelListChemicalСombination = new List<ChemicalСombination>();
                    for (int j = 3; j < valueArray.GetUpperBound(1) - 2; j++)
                    {
                        ChemicalСombination ExcelChemicalСombination = new ChemicalСombination(valueArray[1, j].ToString(), Convert.ToDouble(valueArray[i, j].ToString()));
                        ExcelListChemicalСombination.Add(ExcelChemicalСombination);
                    }
                    Charge ExcelCharge = Charge.Create(valueArray[i, 1].ToString(), ExcelListChemicalСombination, Convert.ToDouble(valueArray[i, valueArray.GetUpperBound(1) - 1].ToString()), Convert.ToDouble(valueArray[i, valueArray.GetUpperBound(1)].ToString()), Convert.ToDouble(valueArray[i, valueArray.GetUpperBound(1) - 2].ToString()));
                    if (Convert.ToDouble(valueArray[i, valueArray.GetUpperBound(1) - 2].ToString()) != 0)
                    {
                        AshName = "A" + valueArray[i, 1].ToString();
                        AshValue = Convert.ToDouble(valueArray[i, valueArray.GetUpperBound(1) - 2].ToString());
                        AshChargeWeight = Convert.ToDouble(valueArray[i, 2].ToString());
                        AshWet = Convert.ToDouble(valueArray[i, valueArray.GetUpperBound(1) - 1].ToString());
                        AshVolatiles = Convert.ToDouble(valueArray[i, valueArray.GetUpperBound(1)].ToString());
                    }

                    double weight;

                    if (valueArray[i, 1].ToString() != AshName)
                    {
                        weight = Convert.ToDouble(valueArray[i, 2].ToString());
                    }
                    else
                    {
                        weight = Math.Round((AshChargeWeight * AshValue * (1 - AshWet / 100 - AshVolatiles / 100)) / 100, 2);
                    }

                    ChargeRowToLoad ExcelRowToLoad = ChargeRowToLoad.Create(ExcelCharge, weight);
                    ExcelChargeRowToLoad.Add(ExcelRowToLoad);
                }

                ExcelLoad = new Load("WorkLoad", ExcelChargeRowToLoad, "t", 0);

                ObjWorkSheet = ObjExcel.Worksheets[2] as Microsoft.Office.Interop.Excel.Worksheet;

                excelRange = ObjWorkSheet.UsedRange;

                valueArray = (object[,])excelRange.get_Value(XlRangeValueDataType.xlRangeValueDefault);

                double blastOutlay = Convert.ToDouble(valueArray[1, 2].ToString());
                double blastPressure = Convert.ToDouble(valueArray[2, 2].ToString());
                double blastTemperature = Convert.ToDouble(valueArray[3, 2].ToString());
                double blastO2Content = Convert.ToDouble(valueArray[4, 2].ToString());
                double naturalWet = Convert.ToDouble(valueArray[5, 2].ToString());
                double topGasPressure = Convert.ToDouble(valueArray[6, 2].ToString());
                double topGasTemperature = Convert.ToDouble(valueArray[7, 2].ToString());
                double topGasCO = Convert.ToDouble(valueArray[8, 2].ToString());
                double topGasCO2 = Convert.ToDouble(valueArray[9, 2].ToString());
                double steamOutlay = Convert.ToDouble(valueArray[10, 2].ToString());
                double naturalGasOutlay = Convert.ToDouble(valueArray[11, 2].ToString());
                double naturalGasCG = Convert.ToDouble(valueArray[12, 2].ToString());
                double naturalGasHG = Convert.ToDouble(valueArray[13, 2].ToString());
                double naturalGasOG = Convert.ToDouble(valueArray[14, 2].ToString());
                double naturalGasNG = Convert.ToDouble(valueArray[15, 2].ToString());
                int tuyeresQuantity = Convert.ToInt32(valueArray[16, 2].ToString());
                int tuyeresDiameter = Convert.ToInt32(valueArray[17, 2].ToString());
                double cokeSampleM25 = Convert.ToDouble(valueArray[18, 2].ToString());
                double cokeSampleM10 = Convert.ToDouble(valueArray[19, 2].ToString());
                double cokeDiameter = Convert.ToDouble(valueArray[20, 2].ToString());
                double cokeDensity = Convert.ToDouble(valueArray[21, 2].ToString());


                ExcelTehnology = Technology.Create(blastOutlay, blastPressure, blastTemperature, blastO2Content,
            naturalWet, topGasPressure, topGasTemperature, topGasCO, topGasCO2,
             steamOutlay, naturalGasOutlay, naturalGasCG, naturalGasHG, naturalGasOG,
             naturalGasNG, tuyeresQuantity, tuyeresDiameter, cokeSampleM25, cokeSampleM10,
             cokeDiameter, cokeDensity);

                ObjWorkSheet = ObjExcel.Worksheets[3] as Microsoft.Office.Interop.Excel.Worksheet;

                excelRange = ObjWorkSheet.UsedRange;

                valueArray = (object[,])excelRange.get_Value(XlRangeValueDataType.xlRangeValueDefault);

                List<ElementDistribution> list = new List<ElementDistribution>();


                for (int j = 2; j < valueArray.GetUpperBound(0)+1; j++)
                {
                    string kind = valueArray[j, 2] == null ? string.Empty : valueArray[j, 2].ToString();
                    string lookInCastIron = valueArray[j, 3] == null ? string.Empty : valueArray[j, 3].ToString();
                    string lookInSlag = valueArray[j, 4] == null ? string.Empty : valueArray[j, 4].ToString();
                    list.Add(new ElementDistribution(valueArray[j, 1].ToString(), kind, lookInCastIron, lookInSlag));
                }

                ExcelDistribution = new ElementsDistribution(list);

                ObjWorkSheet = ObjExcel.Worksheets[4] as Microsoft.Office.Interop.Excel.Worksheet;

                excelRange = ObjWorkSheet.UsedRange;

                valueArray = (object[,])excelRange.get_Value(XlRangeValueDataType.xlRangeValueDefault);

                List<ElementModel> elemModel = new List<ElementModel>();
                List<Coupla> couplaList = null;
                string title = string.Empty, name;
                double distrConst = 0, slagConst = 0, ironConst = 0, qs, phosphor, carbon;

                qs = Convert.ToDouble(valueArray[2, 7].ToString());
                phosphor = Convert.ToDouble(valueArray[2, 8].ToString());
                carbon = Convert.ToDouble(valueArray[2, 9].ToString());
                name = valueArray[2, 10].ToString();

                for (int i = 2; i < valueArray.GetUpperBound(0)+1; i++)
                {
                    if (valueArray[i, 1] != null)
                    {
                        if (i != 2) elemModel.Add(new ElementModel(title, couplaList, distrConst, ironConst, slagConst));

                        title = valueArray[i, 1].ToString();
                        distrConst = Convert.ToDouble(valueArray[i, 4].ToString());
                        ironConst = Convert.ToDouble(valueArray[i, 5].ToString());
                        slagConst = Convert.ToDouble(valueArray[i, 6].ToString());
                        couplaList = new List<Coupla>();
                    }
                    couplaList.Add(Coupla.Create(valueArray[i, 2].ToString(), valueArray[i, 3].ToString().Contains('-') & !valueArray[i, 3].ToString().Contains('E') ? double.Parse(valueArray[i, 3].ToString(), System.Globalization.NumberStyles.Any, CultureInfo.CreateSpecificCulture("en-EN")) : Convert.ToDouble(valueArray[i, 3].ToString())));
                }

                elemModel.Add(new ElementModel(title, couplaList, distrConst, ironConst, slagConst));

                ExcelModel = new Model(name, elemModel, qs, phosphor, carbon);


            }
            catch (Exception ex)
            {
                //MessageBox???
            }
            finally
            {
                ObjWorkBook.Close();
                ObjWorkBook = null;
                ObjWorkSheet = null;
                ObjExcel = null;
                GC.Collect();
            }

        }

        public static void ConvertXmlToXls(Output ExcelOutput, string fileName)
        {
            //string fileName = "C:\\Outlook\\Documentation\\DomainsExampls\\Xls\\Result.xlsx";
            try
            {
                ObjExcel = new Microsoft.Office.Interop.Excel.Application();
                ObjWorkBook = ObjExcel.Workbooks.Add(System.Reflection.Missing.Value);
                ObjWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ObjWorkBook.Sheets[1];

                int count = 1;

                ObjWorkSheet.Cells[1, count] = "Вес";
                ObjWorkSheet.Cells[2, count] = Math.Round(ExcelOutput.CastIronWeight, 3);

                count++;

                foreach (ChemicalСombination item in ExcelOutput.CastIronComposition)
                {
                    ObjWorkSheet.Cells[1, count] = item.Element;
                    ObjWorkSheet.Cells[2, count] = Math.Round(item.Percentage, 3);
                    count++;
                }


                ObjWorkBook.SaveAs(fileName);
                ObjWorkBook.Close();
                ObjWorkBook = null;
                ObjWorkSheet = null;
                ObjExcel = null;
                GC.Collect();
            }
            catch (Exception ex)
            {
                //MessageBox???
            }
        }

        private static Microsoft.Office.Interop.Excel.Application ObjExcel;
        private static Microsoft.Office.Interop.Excel.Workbook ObjWorkBook;
        private static Microsoft.Office.Interop.Excel.Worksheet ObjWorkSheet;
    }
}
